import pymysql
import requests
from concurrent.futures import ThreadPoolExecutor, as_completed
import logging
from datetime import datetime
import re
from queue import Queue
from dbutils.pooled_db import PooledDB
from urllib.parse import unquote

# 配置项
CONFIG = {
    'DB_POOL': {
        'maxconnections': 20,
        'mincached': 5,
        'maxcached': 10,
        'maxshared': 10
    },
    'THREAD_POOL': {
        'workers': 10
    }
}

# 日志配置
log_file = f"image_sync_{datetime.now().strftime('%Y%m%d_%H%M%S')}.log"
logging.basicConfig(
    filename=log_file,
    level=logging.INFO,  # 改为 INFO 级别以查看更多日志
    format='%(asctime)s - %(levelname)s - %(message)s'
)

# 数据库配置
source_db_config = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "123456",
    "database": "zazhi2007",
    "autocommit": True,
}

target_db_config = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "123456",
    "database": "5i40",
    "autocommit": True,
}

# 创建连接池
source_pool = PooledDB(creator=pymysql, **source_db_config, **CONFIG['DB_POOL'])
target_pool = PooledDB(creator=pymysql, **target_db_config, **CONFIG['DB_POOL'])

def check_image_url(url):
    """检查图片URL是否可访问"""
    try:
        if not url:
            return False
            
        # 如果是默认图片，直接返回True
        if url == DEFAULT_IMAGE:
            return True
            
        response = requests.head(url, timeout=5)
        return response.status_code == 200
    except:
        return False
    

def is_404_image(url):
    """检查是否是404的图片路径"""
    if not url:
        return False
    
    # 检查是否匹配特定的404图片模式
    pattern = r'https://zimg\.zazhiejia\.com/images/\d{6}/goods_img/\d+_G_\d+\.jpg'
    if re.match(pattern, url):
        if not check_image_url(url):
            return True
    return False




def process_image_path(path):
    """处理图片路径"""
    if not path:
        return ""
    
    # 如果已经是完整URL，直接返回
    if path.startswith(('http://', 'https://')):
        return path
    
    # 移除开头的斜杠并拼接域名
    path = path.lstrip('/')
    return f"https://zimg.zazhiejia.com/{path}"

def get_gallery_images(goods_id, source_cursor):
    """获取商品相册图片"""
    try:
        source_cursor.execute("""
            SELECT thumb_url, img_url, img_original 
            FROM ecs_goods_gallery 
            WHERE goods_id = %s 
            ORDER BY img_sort ASC, img_id ASC
        """, (goods_id,))
        return source_cursor.fetchall()
    except Exception as e:
        logging.error(f"获取相册图片失败, goods_id: {goods_id}, 错误: {str(e)}")
        return []

def get_invalid_image_goods():
    """获取图片无效的商品"""
    target_conn = None
    try:
        target_conn = target_pool.connection()
        cursor = target_conn.cursor(pymysql.cursors.DictCursor)
        
        # 查询图片为空或无法访问的商品
        cursor.execute("""
            SELECT id, title, image, images 
            FROM fa_shop_goods 
            WHERE image = '' OR images = '' 
            OR image IS NULL OR images IS NULL
        """)
        
        invalid_goods = []
        for goods in cursor.fetchall():
            # 检查现有图片是否可访问
            image_valid = check_image_url(goods['image'])
            images_valid = any(check_image_url(img) for img in goods['images'].split(',') if img)
            
            if not image_valid or not images_valid:
                invalid_goods.append(goods)
                print(f"发现无效图片商品: {goods['title']}")
        
        return invalid_goods
    finally:
        if target_conn:
            target_conn.close()
            

def get_source_goods_images(goods_title, source_cursor):
    """从源数据库获取商品图片"""
    # 通过商品名称查询源数据库
    source_cursor.execute("""
        SELECT goods_id, goods_thumb, goods_img, original_img 
        FROM ecs_goods 
        WHERE goods_name = %s
    """, (goods_title,))
    goods = source_cursor.fetchone()
    
    if not goods:
        return None, []
    
    # 获取相册图片
    source_cursor.execute("""
        SELECT thumb_url, img_url, img_original 
        FROM ecs_goods_gallery 
        WHERE goods_id = %s 
        ORDER BY img_sort ASC, img_id ASC
    """, (goods['goods_id'],))
    gallery = source_cursor.fetchall()
    
    return goods, gallery


def clean_invalid_images(goods):
    """清理商品中无效的图片链接"""
    target_conn = None
    try:
        target_conn = target_pool.connection()
        target_cursor = target_conn.cursor()
        
        need_update = False
        new_image = goods['image']
        
        # 检查主图
        if not new_image or not check_image_url(new_image) or is_404_image(new_image):
            new_image = DEFAULT_IMAGE
            need_update = True
        
        # 获取当前图片列表
        images = goods['images'].split(',') if goods['images'] else []
        valid_images = []
        
        # 检查每个图片是否可访问
        for img in images:
            if img and check_image_url(img) and not is_404_image(img):
                valid_images.append(img)
        
        # 如果所有图片都无效，使用默认图片
        if not valid_images:
            valid_images = [DEFAULT_IMAGE]
            need_update = True
        
        # 如果有无效图片或需要更新，更新数据库
        if need_update or len(valid_images) != len(images):
            new_images = ','.join(valid_images)
            target_cursor.execute("""
                UPDATE fa_shop_goods 
                SET image = %s, images = %s 
                WHERE title = %s
            """, (new_image, new_images, goods['title']))
            target_conn.commit()
            print(f"已清理商品 [{goods['title']}] 的无效图片")
            
        return valid_images
        
    except Exception as e:
        logging.error(f"清理商品 [{goods['title']}] 图片时发生错误: {str(e)}")
        print(f"清理商品 [{goods['title']}] 图片时发生错误: {str(e)}")
        return []
    finally:
        if target_conn:
            target_conn.close()
# 添加默认图片常量
DEFAULT_IMAGE = "https://zimg.zazhiejia.com/assets/addons/shop/img/noimage.jpg"

def process_single_goods(goods):
    """处理单个商品的图片"""
    source_conn = None
    target_conn = None
    try:
        # 先清理无效图片
        valid_images = clean_invalid_images(goods)
        
        # 如果已经使用了默认图片，不需要继续处理
        if valid_images == [DEFAULT_IMAGE]:
            return
            
        # 检查主图和相册图是否需要更新
        need_update = False
        if not goods['image'] or not check_image_url(goods['image']) or is_404_image(goods['image']):
            need_update = True
        if not valid_images:
            need_update = True
            
        if not need_update:
            return
            
        # 需要更新，从源数据库获取图片
        source_conn = source_pool.connection()
        target_conn = target_pool.connection()
        source_cursor = source_conn.cursor(pymysql.cursors.DictCursor)
        target_cursor = target_conn.cursor()

        # 通过商品名查询源数据
        source_cursor.execute("""
            SELECT goods_thumb, goods_img, original_img, goods_id
            FROM ecs_goods 
            WHERE goods_name = %s
        """, (goods['title'],))
        source_images = source_cursor.fetchone()
        
        if not source_images:
            # 如果找不到源数据，使用默认图片
            target_cursor.execute("""
                UPDATE fa_shop_goods 
                SET image = %s, images = %s 
                WHERE title = %s
            """, (DEFAULT_IMAGE, DEFAULT_IMAGE, goods['title']))
            target_conn.commit()
            print(f"未找到商品 [{goods['title']}] 的源数据，已设置默认图片")
            return

        # 处理图片路径
        new_image = process_image_path(source_images['goods_thumb'])
        new_images = []
        
        # 如果主图无效，尝试获取相册图片
        if not check_image_url(new_image) or is_404_image(new_image):
            gallery_images = get_gallery_images(source_images['goods_id'], source_cursor)
            if gallery_images:
                first_gallery = gallery_images[0]
                new_image = process_image_path(first_gallery['thumb_url'])
                
                for gallery in gallery_images:
                    img_url = process_image_path(gallery['img_url'])
                    if check_image_url(img_url) and not is_404_image(img_url):
                        new_images.append(img_url)

        # 如果没有找到有效图片，使用默认图片
        if not check_image_url(new_image) or is_404_image(new_image):
            new_image = DEFAULT_IMAGE
            new_images = [DEFAULT_IMAGE]
        elif not new_images:
            new_images = [new_image]

        # 更新数据库
        images_str = ','.join(new_images)
        target_cursor.execute("""
            UPDATE fa_shop_goods 
            SET image = %s, images = %s 
            WHERE title = %s
        """, (new_image, images_str, goods['title']))
        
        target_conn.commit()
        print(f"已更新商品图片: [{goods['title']}]")
            
    except Exception as e:
        logging.error(f"处理商品 [{goods['title']}] 时发生错误: {str(e)}")
        print(f"处理商品 [{goods['title']}] 时发生错误: {str(e)}")
    finally:
        if source_conn:
            source_conn.close()
        if target_conn:
            target_conn.close()

def main():
    """主函数"""
    try:
        print("开始同步商品图片...")
        
         # 获取所有商品列表
        target_conn = target_pool.connection()
        target_cursor = target_conn.cursor(pymysql.cursors.DictCursor)
        
        # 查询所有商品
        target_cursor.execute("SELECT title, image, images FROM fa_shop_goods")
        goods_list = target_cursor.fetchall()
        
        total_goods = len(goods_list)
        print(f"共有 {total_goods} 个商品需要处理")

        # 使用线程池处理
        with ThreadPoolExecutor(max_workers=CONFIG['THREAD_POOL']['workers']) as executor:
            futures = [executor.submit(process_single_goods, goods) for goods in goods_list]
            
            for i, future in enumerate(as_completed(futures), 1):
                try:
                    future.result()
                    if i % 10 == 0:
                        print(f"进度: {i}/{total_goods}")
                except Exception as e:
                    logging.error(f"任务执行失败: {str(e)}")

        print("商品图片同步完成！")
        
    except Exception as e:
        logging.error(f"同步过程中发生错误: {str(e)}")
        print(f"同步过程中发生错误: {str(e)}")
    finally:
        if target_conn:
            target_conn.close()

if __name__ == "__main__":
    main()